--    Program: 		CAPPOEXT_PERSON_ASSIGNMENT.sql
--    Description:	Extract PO Person (Employee) Assignment information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
   
   l_set_of_books_name		varchar2(20);
   l_set_of_books_id        number;
                    		  
   cursor cur_po_persons(p_sob_name varchar2) is
      select paa.* 
      from   per_all_assignments_f paa,
      	     hr_organization_units hou
      where  paa.business_group_id = hou.business_group_id
      and    upper(hou.name) = 'BG_'||upper(substr(p_sob_name, -4));

begin
   l_pathname := '/tmp';
   l_filename := 'CAPPO_PEROSN_ASSIGNMEN.dat';
   l_utl_file := utl_file.fopen(l_pathname,l_filename,'w',32767);
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_set_of_books_name := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_set_of_books_name);  
   exception
      when others then
        dbms_output.put_line(l_set_of_books_name||': set of books name not found');
   end; 

   for cur in cur_po_persons(l_set_of_books_name) loop   	
    
	 -- output data
     utl_file.put_line( l_utl_file, 
       cur.ASSIGNMENT_ID                   || chr(9) ||
       cur.EFFECTIVE_START_DATE            || chr(9) ||
       cur.EFFECTIVE_END_DATE              || chr(9) ||
       cur.BUSINESS_GROUP_ID               || chr(9) ||
       cur.RECRUITER_ID                    || chr(9) ||
       cur.GRADE_ID                        || chr(9) ||
       cur.POSITION_ID                     || chr(9) ||
       cur.JOB_ID                          || chr(9) ||
       cur.ASSIGNMENT_STATUS_TYPE_ID       || chr(9) ||
       cur.PAYROLL_ID                      || chr(9) ||
       cur.LOCATION_ID                     || chr(9) ||
       cur.PERSON_REFERRED_BY_ID           || chr(9) ||
       cur.SUPERVISOR_ID                   || chr(9) ||
       cur.SPECIAL_CEILING_STEP_ID         || chr(9) ||
       cur.PERSON_ID                       || chr(9) ||
       cur.RECRUITMENT_ACTIVITY_ID         || chr(9) ||
       cur.SOURCE_ORGANIZATION_ID          || chr(9) ||
       cur.ORGANIZATION_ID                 || chr(9) ||
       cur.PEOPLE_GROUP_ID                 || chr(9) ||
       cur.SOFT_CODING_KEYFLEX_ID          || chr(9) ||
       cur.VACANCY_ID                      || chr(9) ||
       cur.PAY_BASIS_ID                    || chr(9) ||
       cur.ASSIGNMENT_SEQUENCE             || chr(9) ||
       cur.ASSIGNMENT_TYPE                 || chr(9) ||
       cur.PRIMARY_FLAG                    || chr(9) ||
       cur.APPLICATION_ID                  || chr(9) ||
       cur.ASSIGNMENT_NUMBER               || chr(9) ||
       cur.CHANGE_REASON                   || chr(9) ||
       cur.COMMENT_ID                      || chr(9) ||
       cur.DATE_PROBATION_END              || chr(9) ||
       cur.DEFAULT_CODE_COMB_ID            || chr(9) ||
       cur.EMPLOYMENT_CATEGORY             || chr(9) ||
       cur.FREQUENCY                       || chr(9) ||
       cur.INTERNAL_ADDRESS_LINE           || chr(9) ||
       cur.MANAGER_FLAG                    || chr(9) ||
       cur.NORMAL_HOURS                    || chr(9) ||
       cur.PERF_REVIEW_PERIOD              || chr(9) ||
       cur.PERF_REVIEW_PERIOD_FREQUENCY    || chr(9) ||
       cur.PERIOD_OF_SERVICE_ID            || chr(9) ||
       cur.PROBATION_PERIOD                || chr(9) ||
       cur.PROBATION_UNIT                  || chr(9) ||
       cur.SAL_REVIEW_PERIOD               || chr(9) ||
       cur.SAL_REVIEW_PERIOD_FREQUENCY     || chr(9) ||
       cur.SET_OF_BOOKS_ID                 || chr(9) ||
       cur.SOURCE_TYPE                     || chr(9) ||
       cur.TIME_NORMAL_FINISH              || chr(9) ||
       cur.TIME_NORMAL_START               || chr(9) ||
       cur.REQUEST_ID                      || chr(9) ||
       cur.PROGRAM_APPLICATION_ID          || chr(9) ||
       cur.PROGRAM_ID                      || chr(9) ||
       cur.PROGRAM_UPDATE_DATE             || chr(9) ||
       cur.ASS_ATTRIBUTE_CATEGORY          || chr(9) ||
       cur.ASS_ATTRIBUTE1                  || chr(9) ||
       cur.ASS_ATTRIBUTE2                  || chr(9) ||
       cur.ASS_ATTRIBUTE3                  || chr(9) ||
       cur.ASS_ATTRIBUTE4                  || chr(9) ||
       cur.ASS_ATTRIBUTE5                  || chr(9) ||
       cur.ASS_ATTRIBUTE6                  || chr(9) ||
       cur.ASS_ATTRIBUTE7                  || chr(9) ||
       cur.ASS_ATTRIBUTE8                  || chr(9) ||
       cur.ASS_ATTRIBUTE9                  || chr(9) ||
       cur.ASS_ATTRIBUTE10                 || chr(9) ||
       cur.ASS_ATTRIBUTE11                 || chr(9) ||
       cur.ASS_ATTRIBUTE12                 || chr(9) ||
       cur.ASS_ATTRIBUTE13                 || chr(9) ||
       cur.ASS_ATTRIBUTE14                 || chr(9) ||
       cur.ASS_ATTRIBUTE15                 || chr(9) ||
       cur.ASS_ATTRIBUTE16                 || chr(9) ||
       cur.ASS_ATTRIBUTE17                 || chr(9) ||
       cur.ASS_ATTRIBUTE18                 || chr(9) ||
       cur.ASS_ATTRIBUTE19                 || chr(9) ||
       cur.ASS_ATTRIBUTE20                 || chr(9) ||
       cur.ASS_ATTRIBUTE21                 || chr(9) ||
       cur.ASS_ATTRIBUTE22                 || chr(9) ||
       cur.ASS_ATTRIBUTE23                 || chr(9) ||
       cur.ASS_ATTRIBUTE24                 || chr(9) ||
       cur.ASS_ATTRIBUTE25                 || chr(9) ||
       cur.ASS_ATTRIBUTE26                 || chr(9) ||
       cur.ASS_ATTRIBUTE27                 || chr(9) ||
       cur.ASS_ATTRIBUTE28                 || chr(9) ||
       cur.ASS_ATTRIBUTE29                 || chr(9) ||
       cur.ASS_ATTRIBUTE30                 || chr(9) ||
       cur.LAST_UPDATE_DATE                || chr(9) ||
       cur.LAST_UPDATED_BY                 || chr(9) ||
       cur.LAST_UPDATE_LOGIN               || chr(9) ||
       cur.CREATED_BY                      || chr(9) ||
       cur.CREATION_DATE                   || chr(9) ||
       cur.TITLE                           || chr(9) ||
       cur.OBJECT_VERSION_NUMBER           || chr(9) ||
       cur.BARGAINING_UNIT_CODE            || chr(9) ||
       cur.LABOUR_UNION_MEMBER_FLAG        || chr(9) ||
       cur.HOURLY_SALARIED_CODE            || chr(9) ||
       cur.CONTRACT_ID                     || chr(9) ||
       cur.COLLECTIVE_AGREEMENT_ID         || chr(9) ||
       cur.CAGR_ID_FLEX_NUM                || chr(9) ||
       cur.CAGR_GRADE_DEF_ID               || chr(9) ||
       cur.ESTABLISHMENT_ID                || chr(9) ||
       cur.NOTICE_PERIOD                   || chr(9) ||
       cur.NOTICE_PERIOD_UOM               || chr(9) ||
       cur.EMPLOYEE_CATEGORY               || chr(9) ||
       cur.WORK_AT_HOME                    || chr(9) ||
       cur.JOB_POST_SOURCE_NAME            || chr(9) ||
       cur.POSTING_CONTENT_ID              || chr(9) ||
       cur.PERIOD_OF_PLACEMENT_DATE_START  || chr(9) ||
       cur.VENDOR_ID                       || chr(9) ||
       cur.VENDOR_EMPLOYEE_NUMBER          || chr(9) ||
       cur.VENDOR_ASSIGNMENT_NUMBER        || chr(9) ||
       cur.ASSIGNMENT_CATEGORY             || chr(9) ||
       cur.PROJECT_TITLE                   || chr(9) ||
       cur.APPLICANT_RANK 
	 );
	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := 'Po Person Assignment not exists';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ( 'CAPPOEXT_PERSON_ASSIGNMEN', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg ); 
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
